<?php

class DBHandler {
	
	private static $instance = null;
	
	private $username = '';
	
	private $password = '';
	
	private $db = '';
	
	private $host = 'localhost';
	
	private $handle = null;
	
	/**
	 * Class constructor 
	 */
	private function __construct() {
	}
	
	private function getFieldSet($tableName) {
		$result = null;
		$res = mysql_query('SHOW FIELDS FROM ' . $tableName, $this->handle);
		while ($row = mysql_fetch_array($res)){
			$field = $row['Field'];
			$type = $row['Type'];
			$null = $row['Null'];
			$key = $row['Key'];
			$default = $row['Default'];
			$extra = $row['Extra'];
			$result[$field] = array('type' => $type, 'null' => $null, 'key' => $key, 
									'default' => $default, 'extra' => $extra);
		}
		return $result;
	}
	
	private function startTransaction(){
		$this->handle = mysql_connect($this->host, $this->username, $this->password);
		return mysql_select_db($this->db, $this->handle) or die(mysql_error());		
	}
	
	private function closeTransaction(){
		return mysql_close($this->handle) or die(mysql_error());
	}	
	
	
	/**
	 * Instance initialisation
	 * 
	 * @return instance of DBHandler class;
	 */
	public static function getInstance() {
		self::$instance = self::$instance ? self::$instance : new DBHandler ( );
		return self::$instance;
	}
	
	/**
	 * Defines the DB to use
	 *
	 * @param string $dbName
	 * @return void;
	 */
	
	public function setDB($dbName) {
		$this->db = $dbName;
	}
	
	/**
	 * Defines the DB username
	 *
	 * @param string $username
	 * @return void;
	 */
	public function setUsername($username) {
		$this->username = $username;
	}
	
	/**
	 * Defines the DB password
	 *
	 * @param string $password
	 * @return void;
	 */
	public function setPassword($password) {
		$this->password = $password;
	}
	
	/**
	 * Defines the DB host. By default it is set to "localhost". 
	 * Use this function only in case if your DB is located on different host.  
	 *
	 * @param string $host
	 * @return void;
	 */
	public function setHost($host) {
		$this->username = $host;
	}
	
	
	/**
	 * Inserts new record into table;
	 *
	 * @param string $tableName
	 * @param mixed $dataArray
	 */
	
	public function executeINSERTQuery($tableName, $dataArray) {

		$this->startTransaction();
		$fieldset = $this->getFieldSet($tableName);

		$fieldset = array_change_key_case($fieldset, CASE_LOWER);
		$dataArray = array_change_key_case($dataArray, CASE_LOWER);
		
		$dataArray = array_intersect_key($dataArray, $fieldset);

		foreach ($dataArray as $key => $value){
			$dataArray[$key] = '"' . mysql_real_escape_string($value) . '"'; 
		}
		
		$sql = 'INSERT INTO ' . $tableName . ' (' . implode(', ', array_keys($dataArray)) . ') ';
		$sql .= 'VALUES (' . implode(', ', array_values($dataArray)) . ')';
		
		$result = mysql_query($sql, $this->handle);
		if($result){
			$result = mysql_insert_id($this->handle);	
		}else{
			die(mysql_error() . "<br/>\r\n $sql");
		}
				
		$this->closeTransaction();				
		return $result;
	}
	
	public function executeSELECTQuery($tableName, $fields = null, $where = null, $order = null, $limit = null) {
		$this->startTransaction();
		$result = array();
		
		$sql = isset($fields) && trim($fields) != '' ? 'SELECT ' . $fields . ' FROM ' . $tableName . ' ' : 'SELECT * FROM ' . $tableName . ' ';
		$sql .= isset($where) && trim($where) != '' ? 'WHERE ' . $where . ' ' : '';
		$sql .= isset($order) && trim($order) != '' ? 'ORDER BY ' . $order . ' ' : '';
		$sql .= isset($limit) && trim($limit) != '' ? 'LIMIT ' . $limit . ' ' : '';		
		
		$res = mysql_query($sql, $this->handle);
		
		$i = 0;
		while ($row = mysql_fetch_array($res)){
			$result[$i] = $row;
			$i++; 
		}
		
		$this->closeTransaction();
		return $result;
	}
	

}

?>